﻿using Model;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Utility;
using Utility.Helper;

namespace BLL
{
    /// <summary>
    /// 月计划
    /// </summary>
    public class QS_PLANMONTHAPPLYBLL : EntityBLL<QS_PLANMONTHAPPLY>, IPlanMonthApply
    {
        //
        public QS_PLANMONTHAPPLYBLL(Service service)
        {
            this.Service = service;
        }

        public QS_PLANMONTHAPPLYBLL()
        {
        }

        public DataTable GetDataBind(string buid, string logname, string year = "0", string month = "0")
        {
            var sql = @"SELECT
	T2.N_PROID,
	T2.N_MDETAIL_ID,
	T2.N_MONTHAPP_ID,
	T2.C_PRONAME,
	T2.N_TYPE,
	T2.C_REMARK,
	T2.C_REMARK2,
	T2.N_IF_DETAILS AS IF_DETAILS,
	T2.C_DETAILS_CODE AS DETAILS_CODE,
	T2.N_PARENTID,
	T2.N_NUM1,
	T2.N_NUM2,
	ROUND(
		(CASE
			WHEN T2.N_SYAMOUNT IS NULL THEN 0
			ELSE T2.N_SYAMOUNT
		END),
		6
	) AS N_SYAMOUNT,
	ROUND(
		(CASE
			WHEN T2.N_USEAMOUNT IS NULL THEN 0
			ELSE T2.N_USEAMOUNT
		END),
		6
	) AS N_USEAMOUNT,
	ROUND(
		(CASE
			WHEN T2.N_AMOUNT IS NULL THEN 0
			ELSE T2.N_AMOUNT
		END),
		6
	) AS N_AMOUNT,
	ROUND(
		(CASE
			WHEN T2.N_AMOUNT1 IS NULL THEN 0
			ELSE T2.N_AMOUNT1
		END),
		6
	) AS N_AMOUNT1,
	ROUND(
		(CASE
			WHEN T2.N_AMOUNT2 IS NULL THEN 0
			ELSE T2.N_AMOUNT2
		END),
		6
	) AS N_AMOUNT2,
	ROUND(
		SUM(
			CASE
				WHEN T5.N_AMOUNT IS NULL THEN 0
				ELSE T5.N_AMOUNT
			END
		),
		6
	) AS N_AMOUNT_WEEK,
	ROUND(
		SUM(
			CASE
				WHEN T5.N_USEAMOUNT IS NULL THEN 0
				ELSE T5.N_USEAMOUNT
			END
		),
		6
	) AS N_USEAMOUNT_WEEK,
	CASE
		WHEN
			ROUND(
			SUM(
				CASE
					WHEN T5.N_AMOUNT IS NULL THEN 0
					ELSE T5.N_AMOUNT
				END
			),
			6
		) = 0 THEN '0%'
		WHEN
			SUM
			(
				CASE
					WHEN T5.N_USEAMOUNT IS NULL THEN 0
					ELSE T5.N_USEAMOUNT
				END
			) = 0 THEN '0%'
		ELSE
			TO_CHAR(
				ROUND(
					SUM(
							CASE
								WHEN T5.N_USEAMOUNT IS NULL THEN 0
								ELSE T5.N_USEAMOUNT
							END
						)*100/SUM(
							CASE
								WHEN T5.N_AMOUNT IS NULL THEN 0
								ELSE T5.N_AMOUNT
							END
						),2))||'%'
	END AS N_AMOUNTPROBABILITY
FROM QS_PLANMONTHAPPLY T1
INNER JOIN QS_PLANMONTH_DETAIL T2 ON T1.N_MONTHAPP_ID = T2.N_MONTHAPP_ID
INNER JOIN QS_BUSINESS T3 ON T1.N_BUSINESS_ID = T3.N_BUSINESS_ID
LEFT JOIN QS_PLANWEEKAPPLY T4 ON T1.N_MONTHAPP_ID = T4.N_MONTHAPP_ID AND T4.N_BUSINESS_STATE = 2
LEFT JOIN QS_PLANWEEK_DETAIL T5 ON T5.N_WEEKAPP_ID = T4.N_WEEKAPP_ID AND T2.N_PROID = T5.N_PROID
WHERE
	T3.N_BUSINESS_ID = :BUSINESS_ID
GROUP BY
	T2.N_PROID,
	T2.N_MDETAIL_ID,
	T2.N_MONTHAPP_ID,
	T2.C_PRONAME,
	T2.N_TYPE,
	T2.C_REMARK,
	T2.C_REMARK2,
	T2.N_IF_DETAILS,
	T2.C_DETAILS_CODE,
	T2.N_PARENTID,
	T2.N_NUM1,
	T2.N_NUM2,
	T2.N_AMOUNT,
	T2.N_AMOUNT1,
	T2.N_AMOUNT2,
	T2.N_SYAMOUNT,
	T2.N_USEAMOUNT
ORDER BY
	T2.N_MDETAIL_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":BUSINESS_ID", Convert.ToInt32(buid), "int");
            var dt = Query(sql, parms);
            return dt;
            //     string sql = @"select t.n_proid,
            //t.n_mdetail_id,
            //t.n_monthapp_id,
            //t.c_proname,
            //to_char(decode(N_SYAMOUNT, 0, null, N_SYAMOUNT),'FM9999999999.00') N_SYAMOUNT,";
            //sql += " decode(N_USEAMOUNT, 0, null, N_USEAMOUNT) N_USEAMOUNT,";
            /*if (year != "0" && month != "0")
            {
                sql += @"(select sum(w.n_useamount)
          from qs_planweek_detail w, qs_planweekapply wp
         where wp.n_weekapp_id = w.n_weekapp_id
           and wp.n_year = " + year + @"
           and wp.n_month = " + month + @"
           and w.n_proid = t.n_proid) N_USEAMOUNT,";
            }
            else
            {
                sql += " decode(N_USEAMOUNT, 0, null, N_USEAMOUNT) N_USEAMOUNT,";
            }*/
            //           sql += @"to_char(decode(t.n_amount, 0, null, n_amount),'FM9999999999.00') n_amount,
            //      to_char(decode(t.n_amount1, 0, null, n_amount1), 'FM9999999999.00') n_amount1,
            //      to_char(decode(t.n_amount2, 0, null, n_amount2), 'FM9999999999.00') n_amount2,
            //      t.n_type,
            //      t.c_remark,
            //      t.c_remark2,
            //      t.n_if_details IF_DETAILS,
            //      t.c_details_code DETAILS_CODE,
            //      pro.N_PARENTID,
            //      pro.N_NUM1,
            //      pro.N_NUM2
            // from qs_planmonth_detail t, qs_business b, qs_project pro
            //where t.n_business_id = b.n_business_id
            //  and t.n_proid = pro.n_proid
            //  and b.n_business_id = " + buid + @"
            //order by t.n_mdetail_id ";
            sql = @"       select t.n_proid,
       t.n_mdetail_id,
       t.n_monthapp_id,
       t.c_proname,
       to_char(decode(t.N_SYAMOUNT, 0, null, t.N_SYAMOUNT),'FM9999999999.00') N_SYAMOUNT,
       decode(t.N_USEAMOUNT, 0, null, t.N_USEAMOUNT) N_USEAMOUNT,
       to_char(decode(t.n_amount, 0, null, t.n_amount),'FM9999999999.00') n_amount,
       to_char(decode(t.n_amount1, 0, null, t.n_amount1), 'FM9999999999.00') n_amount1,
       to_char(decode(t.n_amount2, 0, null, t.n_amount2), 'FM9999999999.00') n_amount2,
       round(sum(pwd.n_amount),2) n_amount_week,
       round(sum(pwd.n_useamount),2) n_useamount_week,
       decode(sum(pwd.n_amount),0,null,round(sum(pwd.n_useamount)/sum(pwd.n_amount)*100,2)||'%') n_amountProbability,
       t.n_type,
       t.c_remark,
       t.c_remark2,
       t.n_if_details IF_DETAILS,
       t.c_details_code DETAILS_CODE,
       pro.N_PARENTID,
       pro.N_NUM1,
       pro.N_NUM2
  from qs_business b,qs_project pro,qs_planmonth_detail t,QS_PLANWEEKAPPLY pw,QS_PLANWEEK_DETAIL pwd
 where t.n_monthapp_id = pw.n_monthapp_id
   and pw.n_business_state = 2
   and pw.n_weekapp_id = pwd.n_weekapp_id
   and pwd.n_proid = t.n_proid
   and t.n_proid = pro.n_proid
   and t.n_business_id = b.n_business_id
   and b.n_business_id = " + buid + @"
   group by t.n_proid,pwd.n_proid,t.n_mdetail_id,t.n_monthapp_id,t.c_proname,t.n_amount,t.n_amount1,t.n_amount2,t.n_type,t.c_remark,t.c_remark2,t.n_if_details,t.c_details_code,t.N_SYAMOUNT,t.N_USEAMOUNT,pro.N_PARENTID,pro.N_NUM1,pro.N_NUM2 order by t.n_mdetail_id ";

            //DataTable dt = Query(sql, "", "", logname, logname);
            dt = base.ThkService.Query(sql, "", "", logname, logname);

            if (dt.Rows.Count == 0)
            {
                sql = @"select t.n_proid,
                t.n_mdetail_id,
                t.n_monthapp_id,
                t.c_proname,
                to_char(decode(N_SYAMOUNT, 0, null, N_SYAMOUNT),'FM9999999999.00') N_SYAMOUNT,
                decode(N_USEAMOUNT, 0, null, N_USEAMOUNT) N_USEAMOUNT,
                to_char(decode(t.n_amount, 0, null, n_amount),'FM9999999999.00') n_amount,
                to_char(decode(t.n_amount1, 0, null, n_amount1), 'FM9999999999.00') n_amount1,
                to_char(decode(t.n_amount2, 0, null, n_amount2), 'FM9999999999.00') n_amount2,
                null n_amount_week,
                null n_useamount_week,
                null n_amountProbability,
                t.n_type,
                t.c_remark,
                t.c_remark2,
                t.n_if_details IF_DETAILS,
                t.c_details_code DETAILS_CODE,
                pro.N_PARENTID,
                pro.N_NUM1,
                pro.N_NUM2
                 from qs_planmonth_detail t, qs_business b, qs_project pro
                where t.n_business_id = b.n_business_id
                  and t.n_proid = pro.n_proid
                  and b.n_business_id = " + buid + @"
                order by t.n_mdetail_id";
            }

            dt = base.ThkService.Query(sql, "", "", logname, logname);
            var dt1 = dt.Clone();
            /*********递归排序**********/
            Inno(dt1, dt, 0);
            return dt;
        }

        private void Inno(DataTable t1, DataTable t2, int pid)
        {
            foreach (var row in t2.AsEnumerable().Where(a => Convert.ToInt32(a["N_PARENTID"]) == pid).OrderBy(a => Convert.ToInt32(a["N_NUM1"])).ThenBy(a => Convert.ToInt32(a["N_NUM2"])))
            {
                var entity = t1.NewRow();
                entity["n_proid"] = row["n_proid"];
                entity["n_mdetail_id"] = row["n_mdetail_id"];
                entity["n_monthapp_id"] = row["n_monthapp_id"];
                entity["c_proname"] = row["c_proname"];
                entity["N_SYAMOUNT"] = row["N_SYAMOUNT"];
                entity["N_USEAMOUNT"] = row["N_USEAMOUNT"];
                entity["n_amount"] = row["n_amount"];
                entity["n_amount1"] = row["n_amount1"];
                entity["n_amount2"] = row["n_amount2"];
                entity["n_type"] = row["n_type"];
                entity["c_remark"] = row["c_remark"];
                entity["c_remark2"] = row["c_remark2"];
                entity["IF_DETAILS"] = row["IF_DETAILS"];
                entity["DETAILS_CODE"] = row["DETAILS_CODE"];
                entity["N_PARENTID"] = row["N_PARENTID"];
                entity["N_NUM1"] = row["N_NUM1"];
                entity["N_NUM2"] = row["N_NUM2"];
                entity["n_amount_week"] = row["n_amount_week"];
                entity["n_useamount_week"] = row["n_useamount_week"];
                entity["n_amountProbability"] = row["n_amountProbability"];
                t1.Rows.Add(entity);
                Inno(t1, t2, Convert.ToInt32(entity["n_proid"]));
            }
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="comid"></param>
        /// <param name="year"></param>
        /// <param name="logname"></param>
        /// <returns></returns>
        public DataTable GetDataSyBind(string comid, decimal pcomid, string year, string month, string logname)
        {
            string sql = @"select t.n_proid,
       t.c_proname,
       decode(t.n_state, 1, '有效', '无效') state,
       t.n_num1,
       t.n_num2,
       t.c_remark,
       t.n_parentid,
       t.n_type,
       t.c_pronum,
       t.N_TYPE2,
       t.N_IF_DETAILS IF_DETAILS,
       t.C_DETAILS_CODE DETAILS_CODE,
       nvl(a.jh, 0) syjh,
       nvl(b.useje, 0) useje,
       decode(t.n_type, 0, '主节点', 1, '分节点', 2, '子节点') n_type1
  from qs_project t
  LEFT OUTER JOIN (select n_proid, sum(d.n_amount) jh
                     from qs_planmonthapply p, qs_planmonth_detail d
                    where p.n_monthapp_id = d.n_monthapp_id
                      and p.n_business_state = 2
                      and p.n_company_id = " + comid + @"
                      and p.n_year = " + year + @"
                      and p.n_month <" + month + @"
                    group by d.n_proid) a
    on t.n_proid = a.n_proid
 LEFT OUTER JOIN (select n_proid, sum(d.N_USEAMOUNT) useje
                     from qs_planweekapply w, qs_planweek_detail d
                    where w.n_weekapp_id = d.n_weekapp_id
                      and w.n_business_state = 2
                      and w.n_company_id =  " + comid + @"
                      and w.n_year =  " + year + @"
                      and w.n_month < " + month + @"
                    group by d.n_proid) b
   on t.n_proid = b.n_proid
where  t.cmy_id=" + pcomid + @"
 order by t.n_parentid, t.n_num1, t.n_num2 ";

            DataTable dt = base.ThkService.Query(sql, "", "", logname, logname);

            return dt;
        }

        public DataTable GetDataByAppyId(int comId, int year, int month, int userId)
        {
            var sql = "SELECT * FROM QS_PLANMONTHAPPLY T1 WHERE T1.N_COMPANY_ID=" + comId;
            sql += " AND T1.N_YEAR=" + year;
            sql += " AND T1.N_MONTH=" + month;
            sql += " AND T1.N_USER_ID=" + userId;

            DataTable dt = base.ThkService.Query(sql, "", "BindSelectBy", User.USER_NAME, User.USER_NAME);
            return dt;
        }

        public DataTable GetSeleDataBind(string whr, string logname)
        {
            string sql = @"select * from qs_planmonthapply t where 1=1 " + whr;
            DataTable dt = base.ThkService.Query(sql, "", "", logname, logname);
            return dt;
        }

        /// <summary>
        /// 月计划申请
        /// </summary>
        /// <param name="bModle"></param>
        /// <param name="pModel"></param>
        /// <param name="list"></param>
        /// <param name="tModel"></param>
        /// <param name="NexModel"></param>
        /// <returns></returns>
        public int AppPlanMonth(QS_Business bModle, QS_PLANMONTHAPPLY pModel, List<QS_PLANMONTH_DETAIL> list, Lc_Transaction tModel, Lc_Transaction NexModel, string logname)
        {
            List<string> cList = new List<string>();
            string sql = @"insert into qs_business
  (n_business_id,
   n_business_name,
   n_process_id,
   n_business_state,
   n_business_operator_id,
   n_business_operator_time,
   n_remark,
   n_company_id)
values
  (" + bModle.Business_Id + @",
   '" + bModle.Business_Name + @"',
   " + bModle.Process_id + @",
  " + bModle.Business_State + @",
   " + bModle.Business_operator_Id + @",
   sysdate,
 '" + bModle.Business_remark + @"',
      " + bModle.N_COMPANY_ID + @")";

            cList.Add(sql);

            sql = @"update qs_planmonthapply
   set
       n_business_id = " + pModel.N_BUSINESS_ID + @",
       c_month_name = '" + pModel.C_MONTH_NAME + @"',
       n_company_id = " + pModel.N_COMPANY_ID + @",
       n_user_id =" + pModel.N_USER_ID + @",
       d_operator_time = sysdate,
       c_reamrk = '" + pModel.C_REAMRK + @"',
       n_business_state = " + pModel.N_BUSINESS_STATE + @",
       n_yearapp_id =  " + pModel.N_YEARAPP_ID + @"
 where n_monthapp_id = " + pModel.N_MONTHAPP_ID + "";
            cList.Add(sql);

            sql = @" delete qs_planmonth_detail
  where N_MONTHAPP_ID = " + pModel.N_MONTHAPP_ID;

            cList.Add(sql);
            for (int i = 0; i < list.Count; i++)
            {
                #region 明细

                QS_PLANMONTH_DETAIL dModel = list[i];

                sql = @"
insert into qs_planmonth_detail
  (n_mdetail_id,
   n_monthapp_id,
   n_proid,
   c_proname,
   n_amount,
   c_remark,
   n_operatorid,
   d_operatortime,
   n_business_id,
   n_type,
   n_syamount,
   n_useamount  , C_PRONUM,N_TYPE2,N_IF_DETAILS,C_DETAILS_CODE,N_NUM1,N_NUM2,N_PARENTID,N_AMOUNT1)
values
  (SEQ_MDETAIL_ID.Nextval,
   " + dModel.N_MONTHAPP_ID + @",
   " + dModel.N_PROID + @",
   '" + dModel.C_PRONAME + @"',
   " + dModel.N_AMOUNT + @",
   '" + dModel.C_REMARK + @"',
   " + dModel.N_OPERATORID + @",
   sysdate,
  " + dModel.N_BUSINESS_ID + @",
  " + dModel.N_TYPE + @",
   " + dModel.N_SYAMOUNT + @",
  " + dModel.N_USEAMOUNT + @",'" + dModel.C_PRONUM + @"',
  " + dModel.N_TYPE2 + @"," + dModel.N_IF_DETAILS + @",'" + dModel.C_DETAILS_CODE + @"',
   " + dModel.N_NUM1 + "," + dModel.N_NUM2 + @"," + dModel.N_PARENTID + @"," + dModel.N_AMOUNT1 + @")";

                cList.Add(sql);

                #endregion 明细
            }

            #region MyRegion

            sql = @"insert into lc_transaction
  (n_transaction_id,
   n_process_id,
   n_queue_id,
   n_business_id,
   n_transaction_state,
   n_transaction_comment,
   c_transactor_id,
   d_transaction_time_begin,
   d_transaction_time_end,
   d_transaction_time_due,
   n_transaction_due_state,
   c_transactor_signature,
   n_last_bace_transaction_id)
values
  (n_transaction_id.Nextval,
   " + tModel.N_PROCESS_ID + @",
   " + tModel.N_QUEUE_ID + @",
   " + bModle.Business_Id + @",
   " + tModel.N_TRANSACTION_STATE + @",
   '" + tModel.C_TRANSACTION_COMMENT + @"',
   '" + tModel.C_TRANSACTOR_ID + @"',
   sysdate,
   sysdate,
   TO_DATE('" + tModel.D_TRANSACTION_TIME_DUE + @"','YYYY-MM-DD HH24:MI:SS'),
    " + tModel.N_TRANSACTION_DUE_STATE + @",
   '" + tModel.C_TRANSACTOR_SIGNATURE + @"',
   " + tModel.N_LAST_BACE_TRANSACTION_ID + @")";

            cList.Add(sql);
            sql = @"insert into lc_transaction
  (n_transaction_id,
   n_process_id,
   n_queue_id,
   n_business_id,
   n_transaction_state,
   n_transaction_comment,
   c_transactor_id,
   d_transaction_time_begin,
   d_transaction_time_end,
   d_transaction_time_due,
   n_transaction_due_state,
   c_transactor_signature,
   n_last_bace_transaction_id)
values
  (n_transaction_id.Nextval,
   " + NexModel.N_PROCESS_ID + @",
   " + NexModel.N_QUEUE_ID + @",
   " + bModle.Business_Id + @",
   " + NexModel.N_TRANSACTION_STATE + @",
   '" + NexModel.C_TRANSACTION_COMMENT + @"',
   '" + NexModel.C_TRANSACTOR_ID + @"',
   sysdate,
   TO_DATE('1900-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('" + NexModel.D_TRANSACTION_TIME_DUE + @"','YYYY-MM-DD HH24:MI:SS'),
    " + NexModel.N_TRANSACTION_DUE_STATE + @",
   '" + NexModel.C_TRANSACTOR_SIGNATURE + @"',
   " + NexModel.N_LAST_BACE_TRANSACTION_ID + @")";

            cList.Add(sql);

            #endregion MyRegion

            int rec = base.ThkService.SQLSExcute(cList, "月计划申请", "AppPlanMonth", logname, logname);
            return rec;
        }

        /// <summary>
        /// 根据公司编号查询公司信息
        /// </summary>
        /// <param name="NId"></param>
        /// <returns></returns>
        public Sys_Company GetInfoById(string CId)
        {
            //sql语句
            string sql = string.Format(@"SELECT CMY_ID,CMY_NAME,CMY_NAME2,CMY_LEVEL,CMY_TYPE,CMY_PARENTID,C_CODE FROM SYS_COMPANY WHERE CMY_ID={0}", CId);

            //从数据库获取数据
            DataTable dt = Query(sql);

            //实例化一个公司信息实体类
            Sys_Company model = new Sys_Company();

            if (dt != null && dt.Rows.Count > 0)
            {
                model.CMY_ID = decimal.Parse(dt.Rows[0]["CMY_ID"].ToString());
                model.CMY_NAME = dt.Rows[0]["CMY_NAME"].ToString();
                model.CMY_NAME2 = dt.Rows[0]["CMY_NAME2"].ToString();
                model.CMY_LEVEL = decimal.Parse(dt.Rows[0]["CMY_LEVEL"].ToString());
                model.CMY_TYPE = dt.Rows[0]["CMY_TYPE"].ToString();
                model.CMY_PARENTID = decimal.Parse(dt.Rows[0]["CMY_PARENTID"].ToString());

                model.C_CODE = dt.Rows[0]["C_CODE"].ToString();
            }
            return model;
        }

        /// <summary>
        /// 批量修改月计划明细(计划)
        /// </summary>
        /// <param name="list"></param>
        /// <param name="logname"></param>
        /// <returns></returns>
        public int Updates1(List<QS_PLANMONTH_DETAIL> list, string logname)
        {
            List<string> cList = new List<string>();

            string sql = "";
            for (int i = 0; i < list.Count; i++)
            {
                QS_PLANMONTH_DETAIL model = list[i];
                sql = @" update QS_PLANMONTH_DETAIL
   set n_amount = " + model.N_AMOUNT + @",N_AMOUNT1=" + model.N_AMOUNT1 + @", C_REMARK = '" + model.C_REMARK + @"',
D_OPERATORTIME=sysdate
 where N_MDETAIL_ID = " + model.N_MDETAIL_ID;

                cList.Add(sql);

                //if (i != list.Count - 1)
                //{
                //    sql += ";";
                //}
            }

            int rec = base.ThkService.SQLSExcute(cList, "批量修改月计划明细", "Updates", logname, logname);
            return rec;
            // return 0;
        }

        /// <summary>
        /// 批量修改月计划明细(审批)
        /// </summary>
        /// <param name="list"></param>
        /// <param name="logname"></param>
        /// <returns></returns>
        public int Updates(List<QS_PLANMONTH_DETAIL> list, string logname)
        {
            List<string> cList = new List<string>();

            string sql = "";
            for (int i = 0; i < list.Count; i++)
            {
                QS_PLANMONTH_DETAIL model = list[i];
                sql = @" update QS_PLANMONTH_DETAIL
   set n_amount = " + model.N_AMOUNT + @",N_AMOUNT2=" + model.N_AMOUNT2 + @", C_REMARK2 = '" + model.C_REMARK2 + @"',
D_OPERATORTIME=sysdate
 where N_MDETAIL_ID = " + model.N_MDETAIL_ID;

                cList.Add(sql);

                //if (i != list.Count - 1)
                //{
                //    sql += ";";
                //}
            }

            int rec = base.ThkService.SQLSExcute(cList, "批量修改月计划明细", "Updates", logname, logname);
            return rec;
            // return 0;
        }

        #region 实现

        public override bool Insert(QS_PLANMONTHAPPLY entity)
        {
            entity.N_MONTHAPP_ID = GetSequence("SEQ_MONTHAPP_ID");
            return base.Insert(entity);
        }

        public override bool Update(QS_PLANMONTHAPPLY entity)
        {
            var sql = @"UPDATE QS_PLANMONTHAPPLY
SET
	N_YEAR={1},
	N_MONTH={2},
	C_USEMONTH= '{3}',
	N_BUSINESS_ID = {4},
	C_MONTH_NAME = '{5}',
	N_COMPANY_ID = {6},
	N_USER_ID = {7},
	D_OPERATOR_TIME = TO_DATE('{8}','YYYY-MM-DD HH24:MI:SS'),
	C_REAMRK = '{9}',
	N_BUSINESS_STATE = {10},
	N_YEARAPP_ID = {11}
WHERE
	N_MONTHAPP_ID = {0}";
            sql = string.Format(sql,
                entity.N_MONTHAPP_ID,
                entity.N_YEAR,
                entity.N_MONTH,
                entity.C_USEMONTH,
                entity.N_BUSINESS_ID,
                entity.C_MONTH_NAME,
                entity.N_COMPANY_ID,
                entity.N_USER_ID,
                entity.D_OPERATOR_TIME.ToString("yyyy-MM-dd HH:mm:ss"),
                entity.C_REAMRK,
                entity.N_BUSINESS_STATE,
                entity.N_YEARAPP_ID);
            return NoQuery(sql) > 0 ? true : false;
        }

        public override QS_PLANMONTHAPPLY Find(object key)
        {
            var sql = "SELECT * FROM QS_PLANMONTHAPPLY WHERE N_MONTHAPP_ID = " + key.ToString();
            var dt = Query(sql);
            if (dt != null && dt.Rows.Count == 1)
            {
                return base.ConvertEntity(dt)[0];
            }
            return null;
        }

        #endregion 实现

        #region IPlanMonthApply 成员

        public IResonResult GetData(int comId, int year)
        {
            string sql = @"SELECT T1.N_YEARAPP_ID FROM QX_PLANYEARAPPLY T1 WHERE T1.N_BUSINESS_STATE=2 AND T1.N_YEAR=" + year + " AND T1.N_COMPANY_ID=" + comId;
            var dt = Query(sql);
            if (dt.Rows.Count == 0)
            {
                return new ResonResult("没有发起年度资金计划", ResultType.error);
            }
            int yearId = Convert.ToInt32(dt.Rows[0][0]);
            sql = @"SELECT
	T1.*
FROM
	QS_PROJECT T1
INNER JOIN (
	SELECT DISTINCT
		(T2.N_PROID)
	FROM
		QX_PLANYEARAPPLY T1
	INNER JOIN QX_PLANYEAR_DETAIL T2 ON T1.N_MONTHAPP_ID = T2.N_MONTHAPP_ID
	WHERE
		T1.N_COMPANY_ID =" + comId + @" AND
        T1.N_YEARAPP_ID =" + yearId + @"
) T2 ON T1.N_PROID = T2.N_PROID";
            dt = base.Query(sql);
            //组装业务
            var dt1 = new DataTable();
            dt1.Columns.Add("ProId");//项目ID
            dt1.Columns.Add("ProName");//项目名称
            dt1.Columns.Add("ProType");//项目节点类型
            dt1.Columns.Add("ProPId");//项目上一级ID
            dt1.Columns.Add("ProRemark");//项目备注项
            dt1.Columns.Add("ProIfDetails");//项目是否具备子项
            dt1.Columns.Add("ProDetailsCode");//项目子项管理关键字
            //从顶级递归查询出
            foreach (var item1 in dt.AsEnumerable().Where(a => (a["N_PARENTID"] != DBNull.Value && Convert.ToInt32(a["N_PARENTID"]) == 0)).OrderBy(a => a["N_NUM1"]).ThenBy(a => a["N_NUM2"]))
            {
                var entity1 = dt1.NewRow();
                entity1["ProId"] = Convert.ToInt32(item1["N_PRO_ID"]);
                entity1["ProName"] = Convert.ToInt32(item1["C_PRONAME"]);
                entity1["ProType"] = Convert.ToInt32(item1["N_TYPE"]);
                entity1["ProPId"] = 0;
                entity1["ProRemark"] = Convert.ToString(item1["c_remark"]);
                entity1["ProIfDetails"] = false;
                entity1["ProDetailsCode"] = DBNull.Value;
                dt1.Rows.Add(entity1);
                foreach (var item2 in dt.AsEnumerable().Where(a => (a["N_PARENTID"] != DBNull.Value && Convert.ToInt32(a["N_PARENTID"]) == Convert.ToInt32(item1["N_PRO_ID"]))).OrderBy(a => a["N_NUM1"]).ThenBy(a => a["N_NUM2"]))
                {
                    var entity2 = dt1.NewRow();
                    entity2["ProId"] = Convert.ToInt32(item2["N_PRO_ID"]);
                    entity2["ProName"] = Convert.ToInt32(item2["C_PRONAME"]);
                    entity2["ProType"] = Convert.ToInt32(item2["N_TYPE"]);
                    entity2["ProPId"] = Convert.ToInt32(item1["N_PRO_ID"]);
                    entity2["ProRemark"] = Convert.ToString(item2["c_remark"]);
                    entity2["ProIfDetails"] = false;
                    entity2["ProDetailsCode"] = DBNull.Value;
                    dt1.Rows.Add(entity2);
                    foreach (var item3 in dt.AsEnumerable().Where(a => (a["N_PARENTID"] != DBNull.Value && Convert.ToInt32(a["N_PARENTID"]) == Convert.ToInt32(item2["N_PRO_ID"]))).OrderBy(a => a["N_NUM1"]).ThenBy(a => a["N_NUM2"]))
                    {
                        var entity3 = dt1.NewRow();
                        entity3["ProId"] = Convert.ToInt32(item3["N_PRO_ID"]);
                        entity3["ProName"] = Convert.ToInt32(item3["C_PRONAME"]);
                        entity3["ProType"] = Convert.ToInt32(item3["N_TYPE"]);
                        entity3["ProPId"] = Convert.ToInt32(item2["N_PRO_ID"]);
                        entity3["ProRemark"] = Convert.ToString(item3["c_remark"]);
                        entity3["ProIfDetails"] = item3["N_IF_DETAILS"] == DBNull.Value ? false : (Convert.ToInt32(item3["N_IF_DETAILS"]) == 0 ? false : true);
                        entity3["ProDetailsCode"] = item3["N_DETAILS_CODE"];
                        dt1.Rows.Add(entity3);
                    }
                }
            }
            return new ResonResult("获取成功", ResultType.success, dt1);
        }

        public IResonResult Apply(int applyId, int userid)
        {
            var entity = Find(applyId);
            if (entity == null)
            {
                return new ResonResult("没有找到提交的信息");
            }
            if (entity.N_BUSINESS_STATE == 0)
            {
                entity.N_BUSINESS_STATE = 1;
                if (!Update(entity))
                {
                    return new ResonResult("数据库异常");
                }
                return new ResonResult("成功", ResultType.success);
            }
            return new ResonResult("已提交申请不能重复提交");
        }

        public IResonResult Create(int comId, int year, int month, int userid)
        {
            //先查询是否有完成的
            var sql = "SELECT COUNT(*) FROM QS_PLANMONTHAPPLY T1 WHERE T1.N_COMPANY_ID=" + comId;
            sql += " AND T1.N_YEAR=" + year;
            sql += " AND T1.N_MONTH=" + month;
            sql += " AND T1.N_BUSINESS_STATE=2";
            if (GetCount(sql) > 0)
            {
                return new ResonResult(year + "年" + month + "月资金计划已经申请,不能重复申请", ResultType.error);
            }
            var entity = Find(comId, year, month, userid);
            //并直接把详情表的数据添加进去
            if (entity == null)
            {
                sql = @"SELECT T1.N_YEARAPP_ID FROM QX_PLANYEARAPPLY T1 WHERE T1.N_BUSINESS_STATE=2 AND T1.N_YEAR=" + year + " AND T1.N_COMPANY_ID=" + comId;
                var dt = Query(sql);
                if (dt.Rows.Count == 0)
                {
                    return new ResonResult("没有发起年度资金计划", ResultType.error);
                }
                var company = new Sys_CompanyBLL().Find(comId);
                entity = new QS_PLANMONTHAPPLY();
                entity.N_COMPANY_ID = comId;
                entity.N_BUSINESS_ID = 0;
                entity.N_BUSINESS_STATE = -1;//0为注销
                entity.N_USER_ID = userid;
                entity.N_YEAR = year;
                entity.N_YEARAPP_ID = Convert.ToInt32(dt.Rows[0][0]);
                entity.C_USEMONTH = year.ToString() + month.ToString().PadLeft(2, '0');
                entity.C_MONTH_NAME = entity.C_USEMONTH + "(" + company.CMY_NAME + ")";
                entity.D_OPERATOR_TIME = DateTime.Parse("1900-1-1");
                entity.C_REAMRK = " ";
                entity.N_MONTH = month;
                if (!Insert(entity))
                {
                    return new ResonResult("数据库异常", ResultType.error);
                }
                //把字表添加数据
                //DataTable dt1 = GetDataSyBind(comId.ToString(), year.ToString(), "");
                DataTable dt1 = GetDataSyBind(comId.ToString(), decimal.Parse(comId.ToString()), year.ToString(), month.ToString(), "");

                if (dt1 == null || dt1.Rows.Count == 0)
                {
                    //本公司没有特殊项目，走通用项目
                    dt1 = GetDataSyBind(comId.ToString(), 0, year.ToString(), month.ToString(), "");
                }

                var planMonthDetailBll = new QS_PLANMONTH_DETAILBLL();
                foreach (DataRow row in dt1.Rows)
                {
                    QS_PLANMONTH_DETAIL entity1 = new QS_PLANMONTH_DETAIL();
                    entity1.N_MONTHAPP_ID = entity.N_MONTHAPP_ID;
                    entity1.N_PROID = decimal.Parse(row["n_proid"].ToString());
                    entity1.C_PRONAME = row["c_proname"].ToString();
                    entity1.N_AMOUNT = 0;
                    entity1.C_REMARK = " ";
                    entity1.N_OPERATORID = userid;
                    entity1.N_BUSINESS_ID = 0;
                    entity1.N_TYPE = decimal.Parse(row["n_type"].ToString());
                    entity1.D_OPERATORTIME = DateTime.Parse("1900-1-1");
                    entity1.N_SYAMOUNT = 0;
                    entity1.N_USEAMOUNT = 0;

                    entity1.C_PRONUM = row["C_PRONUM"].ToString();
                    entity1.N_TYPE2 = decimal.Parse(row["N_TYPE2"].ToString());

                    entity1.N_IF_DETAILS = row["IF_DETAILS"].ToString() == "" ? 0 : decimal.Parse(row["IF_DETAILS"].ToString());

                    entity1.C_DETAILS_CODE = row["DETAILS_CODE"].ToString();
                    entity1.N_NUM1 = decimal.Parse(row["N_NUM1"].ToString());
                    entity1.N_NUM2 = decimal.Parse(row["N_NUM2"].ToString());
                    entity1.N_PARENTID = decimal.Parse(row["N_PARENTID"].ToString());
                    entity1.N_AMOUNT1 = 0;
                    entity1.N_AMOUNT2 = 0;
                    entity1.C_REMARK2 = "";
                    planMonthDetailBll.Insert(entity1);
                }
            }
            return new ResonResult("创建成功", ResultType.success, entity);
        }

        #endregion IPlanMonthApply 成员

        public QS_PLANMONTHAPPLY Find(int comId, int year, int month, int userId)
        {
            var sql = "SELECT * FROM QS_PLANMONTHAPPLY T1 WHERE T1.N_COMPANY_ID=" + comId;
            sql += " and N_BUSINESS_STATE<>0  AND T1.N_YEAR=" + year;
            sql += " AND T1.N_MONTH=" + month;
            //sql += " AND T1.N_USER_ID=" + userId;
            return ConvertEntity(Query(sql)).FirstOrDefault();
        }

        public QS_PLANMONTHAPPLY FindByBuid(int bid)
        {
            var sql = "SELECT * FROM QS_PLANMONTHAPPLY T1 WHERE T1.N_BUSINESS_ID=:N_BUSINESS_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":N_BUSINESS_ID", bid, "int");
            var dt = Query(sql, parms);
            return ConvertEntity(dt).FirstOrDefault();
        }

        public Tuple<string, List<Tuple<string, string, string>>> InsertSqlP(QS_PLANMONTHAPPLY entity)
        {
            entity.N_MONTHAPP_ID = GetSequence("SEQ_MONTHAPP_ID");
            var sql = @"INSERT INTO QS_PLANMONTHAPPLY(
                            N_MONTHAPP_ID,
                            N_YEAR,
                            N_MONTH,
                            C_USEMONTH,
                            N_BUSINESS_ID,
                            C_MONTH_NAME,
                            N_COMPANY_ID,
                            N_USER_ID,
                            D_OPERATOR_TIME,
                            C_REAMRK,
                            N_BUSINESS_STATE,
                            N_YEARAPP_ID)
                        VALUES(
                            :N_MONTHAPP_ID,
                            :N_YEAR,:N_MONTH,
                            :C_USEMONTH,
                            :N_BUSINESS_ID,
                            :C_MONTH_NAME,
                            :N_COMPANY_ID,
                            :N_USER_ID,
                            :D_OPERATOR_TIME,
                            :C_REAMRK,
                            :N_BUSINESS_STATE,
                            :N_YEARAPP_ID)";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":N_MONTHAPP_ID", entity.N_MONTHAPP_ID, "int");
            parms.Add(":N_YEAR", entity.N_YEAR, "int");
            parms.Add(":N_MONTH", entity.N_MONTH, "int");
            parms.Add(":C_USEMONTH", entity.C_USEMONTH, "string");
            parms.Add(":N_BUSINESS_ID", entity.N_BUSINESS_ID, "int");
            parms.Add(":C_MONTH_NAME", entity.C_MONTH_NAME, "string");
            parms.Add(":N_COMPANY_ID", entity.N_COMPANY_ID, "int");
            parms.Add(":N_USER_ID", entity.N_USER_ID, "int");
            parms.Add(":D_OPERATOR_TIME", entity.D_OPERATOR_TIME, "datetime");
            parms.Add(":C_REAMRK", entity.C_REAMRK, "string");
            parms.Add(":N_BUSINESS_STATE", entity.N_BUSINESS_STATE, "int");
            parms.Add(":N_YEARAPP_ID", entity.N_YEARAPP_ID, "int");
            return new Tuple<string, List<Tuple<string, string, string>>>(sql, parms);
        }

        public bool Create(DateTime applyDate, int companyId, string title, List<int> applyEditUserIdArry, out QS_PLANMONTHAPPLY apply, out string Msg)
        {
            var sql = "";
            var parms = new List<Tuple<string, string, string>>();
            apply = null;
            Msg = "";

            try
            {
                #region MyRegion

                var bll = new QS_PLANMONTHAPPLYBLL(this.Service);
                var applyDetailBll = new QS_PLANMONTH_DETAILBLL(bll.Service);
                var businessBll = new Qs_BusinessBLL(bll.Service);
                var companyBll = new Sys_CompanyBLL(bll.Service);
                var processBll = new Lc_ProcessBLL(bll.Service);
                var queueBll = new Lc_QueueBLL(bll.Service);
                var conditionBll = new Lc_ConditionBLL(bll.Service);
                var nodeBll = new Lc_NodeBLL(bll.Service);
                var transactionBll = new Lc_TransactionBLL(bll.Service);
                var projectBll = new QS_PROJECTBLL(bll.Service);
                var planYearDetailsBll = new QS_PLANMONTH_DETAILBLL(bll.Service);
                var yearApplyBll = new QX_PLANYEARAPPLYBLL(bll.Service);

                #endregion MyRegion

                var company = companyBll.Find(companyId);
                var applyYear = yearApplyBll.Find(applyDate.Year, companyId);
                //查询年份是否已经申请资金计划。
                if (applyYear == null)
                {
                    Msg = applyDate.Year + "年," + company.CMY_NAME + "没发起资金申请,请先发起年资金计划";
                    return false;
                }
                sql = "SELECT * FROM QS_PLANMONTHAPPLY T1 WHERE T1.N_BUSINESS_STATE != 0 AND T1.N_YEAR = " + applyDate.Year + " AND T1.N_COMPANY_ID = " + companyId + " AND T1.N_MONTH = " + applyDate.Month;
                if (this.GetCount(sql) > 0)
                {
                    Msg = applyDate.Year + "年" + applyDate.Month + "月," + company.CMY_NAME + "已经发起资金申请了。";
                    return false;
                }
                List<Tuple<string, List<Tuple<string, string, string>>>> sqlArry = new List<Tuple<string, List<Tuple<string, string, string>>>>();
                //创建主表
                var process = processBll.GetProcessByCom("QS_PLANMONTHAPPLY", User.USER_NAME);
                if (process == null)
                {
                    Msg = "未设置月资金计划流程";
                    return false;
                }
                var business = new QS_Business();
                business.Business_Name = applyDate.ToString("yyyyMM") + "月计划编制（" + company.CMY_NAME + "）";
                business.Process_id = process.N_PROCESS_ID;// 121;
                business.Business_State = 1;
                business.Business_operator_Id = (int)User.USER_ID;
                business.Business_remark = title.Trim();
                business.N_COMPANY_ID = User.USER_COMPANY;
                business.Business_operator_date = DateTime.Now;
                sqlArry.Add(businessBll.InsertSqlP(business));
                //新增主表
                apply = new QS_PLANMONTHAPPLY();
                apply.N_YEAR = applyDate.Year;
                apply.N_MONTH = applyDate.Month;
                apply.C_USEMONTH = applyDate.ToString("yyyyMM");
                apply.N_BUSINESS_ID = business.Business_Id;
                apply.C_MONTH_NAME = applyDate.ToString("yyyyMM") + "（" + company.CMY_NAME + "）";
                apply.N_COMPANY_ID = companyId;
                apply.N_USER_ID = User.USER_ID;
                apply.C_REAMRK = title.Trim();
                apply.D_OPERATOR_TIME = DateTime.Now;
                apply.N_BUSINESS_STATE = 1;
                apply.N_YEARAPP_ID = applyYear.N_YEARAPP_ID;
                //apply.N_MONTHAPP_ID = applyYear.N_MONTHAPP_ID;
                sqlArry.Add(this.InsertSqlP(apply));

                Lc_Transaction transaction = new Lc_Transaction();
                transaction.N_PROCESS_ID = business.Process_id;
                //获取第一步队列id
                transaction.N_QUEUE_ID = 176;
                transaction.N_business_id = business.Business_Id;
                transaction.C_TRANSACTION_COMMENT = "月计划申请";
                transaction.C_TRANSACTOR_ID = User.USER_ID.ToString();
                transaction.D_TRANSACTION_TIME_BEGIN = DateTime.Now;
                transaction.D_TRANSACTION_TIME_END = DateTime.Now;
                transaction.D_TRANSACTION_TIME_DUE = DateTime.Parse("1900-1-1");
                transaction.N_TRANSACTION_STATE = 1;
                transaction.C_TRANSACTOR_SIGNATURE = "";
                transaction.N_LAST_BACE_TRANSACTION_ID = 0;
                transaction.D_TRANSACTION_TIME_BEGIN = DateTime.Now;
                transaction.D_TRANSACTION_TIME_END = DateTime.Now;
                sqlArry.Add(transactionBll.InsertSqlP(transaction));

                {
                    if (applyEditUserIdArry.Count > 0)
                    {
                        Lc_Transaction nextTransaction = new Lc_Transaction();
                        nextTransaction.N_PROCESS_ID = business.Process_id;
                        nextTransaction.N_QUEUE_ID = 213;
                        nextTransaction.N_business_id = business.Business_Id;
                        nextTransaction.C_TRANSACTION_COMMENT = "";
                        nextTransaction.C_TRANSACTOR_ID = applyEditUserIdArry.ToIn();
                        nextTransaction.D_TRANSACTION_TIME_BEGIN = DateTime.Now;
                        nextTransaction.D_TRANSACTION_TIME_END = DateTime.Parse("1900-1-1");
                        Lc_Queue queue = queueBll.GetInfoById(nextTransaction.N_QUEUE_ID.ToString());
                        Lc_Node nodel = nodeBll.GetInfoById(queue.N_NODE_ID.ToString());
                        nextTransaction.D_TRANSACTION_TIME_DUE = DateTime.Now.AddDays((int)nodel.N_DOIING_TIME);
                        nextTransaction.N_TRANSACTION_STATE = 0;
                        nextTransaction.C_TRANSACTOR_SIGNATURE = "";
                        nextTransaction.N_LAST_BACE_TRANSACTION_ID = transaction.N_TRANSACTION_ID;
                        sqlArry.Add(transactionBll.InsertSqlP(nextTransaction));
                    }
                }
                {
                    Lc_Transaction nextTransaction = new Lc_Transaction();
                    nextTransaction.N_PROCESS_ID = business.Process_id;
                    nextTransaction.N_QUEUE_ID = 214;
                    nextTransaction.N_business_id = business.Business_Id;
                    nextTransaction.C_TRANSACTION_COMMENT = "";
                    nextTransaction.C_TRANSACTOR_ID = User.USER_ID.ToString();
                    nextTransaction.D_TRANSACTION_TIME_BEGIN = DateTime.Now;
                    nextTransaction.D_TRANSACTION_TIME_END = DateTime.Parse("1900-1-1");
                    Lc_Queue queue = queueBll.GetInfoById(nextTransaction.N_QUEUE_ID.ToString());
                    Lc_Node nodel = nodeBll.GetInfoById(queue.N_NODE_ID.ToString());
                    nextTransaction.D_TRANSACTION_TIME_DUE = DateTime.Now.AddDays((int)nodel.N_DOIING_TIME);
                    nextTransaction.N_TRANSACTION_STATE = 0;
                    nextTransaction.C_TRANSACTOR_SIGNATURE = "";
                    nextTransaction.N_LAST_BACE_TRANSACTION_ID = transaction.N_TRANSACTION_ID;
                    sqlArry.Add(transactionBll.InsertSqlP(nextTransaction));
                }
                //载入 初始数据
                sql = @"SELECT
                        T1.N_IF_DETAILS AS IF_DETAILS,
                        T1.C_DETAILS_CODE AS DETAILS_CODE,
                        T1.N_PROID AS PROJECT_ID,
                        T1.C_PRONAME AS PROJECT_NAME,
                        T1.N_AMOUNT AS AMOUNT,
                        T1.C_REMARK AS REMARK,
                        T1.N_PARENTID AS PARENT_PROJECT_ID,
                        T1.N_TYPE AS TYPE_CODE,
                        T1.N_TYPE2 AS TYPE_CODE2,
                        T1.C_PRONUM AS PROJECT_NUMBER,
                        T1.N_NUM1 AS ORDER_NUMBER1,
                        T1.N_NUM2 AS ORDER_NUMBER2,
                        (
                            SELECT
                                nvl(SUM(nvl(TT2.N_AMOUNT,0)),0)
                            FROM QS_PLANMONTHAPPLY TT1
                            INNER JOIN QS_PLANMONTH_DETAIL TT2 ON TT1.N_MONTHAPP_ID = TT2.N_MONTHAPP_ID
                            WHERE
                                TT1.N_BUSINESS_STATE = 2 AND
                                TT1.N_COMPANY_ID =:COMPANY_ID AND
                                TT2.N_PROID = T1.N_PROID AND
                                TT1.N_YEAR = :YEAR AND
                                TT1.N_MONTH < :MONTH
                        ) AS APPLY_AMOUNT,
                        (
                            SELECT
                                nvl(SUM(nvl(TT3.N_USEAMOUNT,0)),0)
                            FROM QS_PLANMONTHAPPLY TT1
                            INNER JOIN QS_PLANWEEKAPPLY TT2 ON TT1.N_MONTHAPP_ID = TT2.N_MONTHAPP_ID
                            INNER JOIN QS_PLANWEEK_DETAIL TT3 ON TT3.N_WEEKAPP_ID = TT2.N_WEEKAPP_ID
                            WHERE
                                TT1.N_BUSINESS_STATE = 2 AND
                                TT2.N_BUSINESS_STATE = 2 AND
                                TT1.N_COMPANY_ID =:COMPANY_ID AND
                                TT3.N_PROID = T1.N_PROID AND
                                TT1.N_YEAR = :YEAR AND
                                TT1.N_MONTH < :MONTH
                        ) AS USE_AMOUNT
                        FROM QX_PLANYEAR_DETAIL T1
                    WHERE
                    T1.N_YEARAPP_ID = :APPLY_ID
                    ORDER BY N_YDETAIL_ID ASC";
                parms.Clear();
                parms.Add(":COMPANY_ID", companyId, "int");
                parms.Add(":YEAR", applyDate.Year, "int");
                parms.Add(":MONTH", applyDate.Month, "int");
                parms.Add(":APPLY_ID", applyYear.N_YEARAPP_ID, "int");

                foreach (var item in Query(sql, parms).AsEnumerable())
                {
                    sql = "INSERT INTO QS_PLANMONTH_DETAIL(N_MDETAIL_ID,N_MONTHAPP_ID,N_PROID,C_PRONAME,N_AMOUNT,C_REMARK,N_OPERATORID,D_OPERATORTIME,N_BUSINESS_ID,N_TYPE,N_SYAMOUNT,N_USEAMOUNT,C_PRONUM,N_TYPE2,N_IF_DETAILS,C_DETAILS_CODE,N_NUM1,N_NUM2,N_PARENTID,N_AMOUNT1,N_AMOUNT2,C_REMARK2) VALUES(:N_MDETAIL_ID,:N_MONTHAPP_ID,:N_PROID,:C_PRONAME,:N_AMOUNT,:C_REMARK,:N_OPERATORID,:D_OPERATORTIME,:N_BUSINESS_ID,:N_TYPE,:N_SYAMOUNT,:N_USEAMOUNT,:C_PRONUM,:N_TYPE2,:N_IF_DETAILS,:C_DETAILS_CODE,:N_NUM1,:N_NUM2,:N_PARENTID,:N_AMOUNT1,:N_AMOUNT2,:C_REMARK2)";
                    parms = new List<Tuple<string, string, string>>();
                    parms.Add(":N_MDETAIL_ID", GetSequence("SEQ_MDETAIL_ID"), "int");
                    parms.Add(":N_MONTHAPP_ID", apply.N_MONTHAPP_ID, "int");
                    parms.Add(":N_PROID", item.GetValue<int>("PROJECT_ID"), "int");
                    parms.Add(":C_PRONAME", item.GetValue<string>("PROJECT_NAME"), "string");
                    parms.Add(":N_AMOUNT", 0, "decimal");
                    parms.Add(":C_REMARK", "", "string");
                    parms.Add(":N_OPERATORID", User.USER_ID, "int");
                    parms.Add(":D_OPERATORTIME", DateTime.Now, "datetime");
                    parms.Add(":N_BUSINESS_ID", business.Business_Id, "int");
                    parms.Add(":N_TYPE", item.GetValue<int>("TYPE_CODE"), "int");

                    parms.Add(":N_SYAMOUNT", item.GetValue<int>("APPLY_AMOUNT"), "decimal");
                    parms.Add(":N_USEAMOUNT", item.GetValue<int>("USE_AMOUNT"), "decimal");
                    parms.Add(":C_PRONUM", item.GetValue<string>("PROJECT_NUMBER"), "string");
                    parms.Add(":N_TYPE2", item.GetValue<int>("TYPE_CODE2"), "int");
                    parms.Add(":N_IF_DETAILS", item.GetValue<int?>("IF_DETAILS"), "int");
                    parms.Add(":C_DETAILS_CODE", item.GetValue<string>("DETAILS_CODE"), "string");
                    parms.Add(":N_NUM1", item.GetValue<int>("ORDER_NUMBER1"), "int");
                    parms.Add(":N_NUM2", item.GetValue<int>("ORDER_NUMBER2"), "int");
                    parms.Add(":N_PARENTID", item.GetValue<int>("PARENT_PROJECT_ID"), "decimal");
                    parms.Add(":N_AMOUNT1", null, "decimal");
                    parms.Add(":N_AMOUNT2", null, "decimal");
                    parms.Add(":C_REMARK2", "", "string");
                    sqlArry.Add(new Tuple<string, List<Tuple<string, string, string>>>(sql, parms));
                }
                var conn = GetDbConn();
                this.Service.DbConnection = conn;
                conn.Open();
                this.BeginTran();
                try
                {
                    SQLSExcute(sqlArry);
                    this.Commit();
                    return true;
                }
                catch (Exception)
                {
                    this.Commit(true);
                    throw;
                }
            }
            catch (Exception ex)
            {
                Msg = "异常";
                apply = null;
                return false;
            }
        }

        public QS_PLANMONTHAPPLY Find(decimal id)
        {
            var sql = @"SELECT
                            T1.*
                        FROM QS_PLANMONTHAPPLY T1
                        WHERE
                            T1.N_MONTHAPP_ID =:APPLY_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APPLY_ID", id, "int");
            return ConvertEntity(Query(sql, parms)).FirstOrDefault();
        }

        public QS_PLANMONTHAPPLY Find(int companyId, DateTime applyDate)
        {
            var sql = @"SELECT
                         T1.*
                        FROM QS_PLANMONTHAPPLY T1
                        WHERE
                            T1.N_BUSINESS_STATE = 2 AND
                            T1.N_COMPANY_ID = :COMPANY_ID AND
                            T1.C_USEMONTH = :USE_MONTH";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":COMPANY_ID", companyId, "int");
            parms.Add(":USE_MONTH", applyDate.ToString("yyyyMM"), "string");
            var dt = Query(sql, parms);
            return ConvertEntity(dt).FirstOrDefault();
        }

        public JArray GetData(int applyId)
        {
            var data = new QS_PLANMONTH_DETAILBLL().GetByApplyId(applyId);
            return JArray.FromObject(data);
            //查询数据库是否有金额或者备注不为0或空的
            var sql = "SELECT * FROM QS_PLANMONTH_DETAIL T1 WHERE T1.N_MONTHAPP_ID = " + applyId + " AND T1.N_AMOUNT !=0  ORDER BY T1.N_NUM1,T1.N_NUM2";
            JArray arry = JArray.FromObject(Query(sql));

            //若有一条金额不为零或者备注不为空,则使用自动汇总
            if (arry.Count == 0)
            {
                sql = @"SELECT T1.N_PROJECT_ID N_PROID,T1.C_PROJECT_NAME C_PRONAME,SUM(T1.N_MONEY) N_AMOUNT,(CASE WHEN LENGTH(T1.C_REMARK) != 0 THEN GROUP_CONCAT(T2.USER_NAME,':',T1.C_REMARK) ELSE NULL END) C_REMARK,N_TYPE N_TYPE FROM QS_PLANMonth_DEPARTMENT T1,SYS_USER T2
                       WHERE T1.N_USER_ID = T2.USER_ID AND
                       T1.N_APPLY_ID = " + applyId + " GROUP BY T1.N_PROJECT_ID";
                arry = JArray.FromObject(Query(sql));
            }
            else
            {
                sql = "SELECT * FROM QS_PLANMONTH_DETAIL T1 WHERE T1.N_MONTHAPP_ID = " + applyId + "  ORDER BY T1.N_NUM1,T1.N_NUM2";
                arry = JArray.FromObject(Query(sql));
            }

            return arry;
        }

        public void Update(JArray arry, int applyId)
        {
            //解析为实体并更新
            var sql = @"UPDATE QS_PLANMONTH_DETAIL
                        SET
                            N_AMOUNT = :N_MONEY,
                            C_REMARK = :C_REMARK,
                            N_AMOUNT1 = :N_MONEY,
                            C_REMARK2 = :C_REMARK
                        WHERE
                            N_MONTHAPP_ID = :N_APPLY_ID AND
                            N_PROID = :N_PROJECT_ID";
            var entitys = new List<QX_PLANYEAR_DETAIL>();
            foreach (JObject item in arry)
            {
                var parms = new List<Tuple<string, string, string>>();
                parms.Add(":N_MONEY", item["Money"].ToObject<decimal?>() == null ? 0 : item["Money"].ToObject<decimal>());
                parms.Add(":C_REMARK", item["Remark"].ToObject<string>() == null ? "" : item["Remark"].ToObject<string>());
                parms.Add(":N_APPLY_ID", applyId);
                parms.Add(":N_PROJECT_ID", item["ProjectId"].ToObject<int>());
                NoQuery(sql, parms);
            }
        }

        /// <summary>
        /// 获取月资金信息(主营业营收)
        /// </summary>
        public DataTable GetData_1(int companyId, int year, int month)
        {
            var sql = @"SELECT
                            T2.C_PRONAME,
                            SUM(N_AMOUNT) AS N_AMOUNT,
                            SUM(N_USEAMOUNT) AS N_USEAMOUNT
                        FROM QS_PLANWEEKAPPLY T1
                        INNER JOIN QS_PLAN_DETAIL1 T2 on T1.N_WEEKAPP_ID = T2.N_PLANAPP_ID  AND T2.N_TYPE = 3
                        INNER JOIN QS_PLANMONTHAPPLY T3 ON T1.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        WHERE
                            T3.N_YEAR = :YEAR AND
                            T3.N_MONTH = :MONTH AND
                            T1.N_BUSINESS_STATE = 2 AND
                            T3.N_COMPANY_ID = :COMPANY_ID
                        GROUP BY
                            T2.C_PRONAME
                        ORDER BY
                            T2.C_PRONAME";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":MONTH", month, "int");
            parms.Add(":COMPANY_ID", companyId, "int");
            var dt = Query(sql, parms);
            return dt;
        }

        /// <summary>
        /// 获取月资金信息(天燃气购入)
        /// </summary>
        public DataTable GetData_2(int companyId, int year, int month)
        {
            var sql = @"SELECT
                            T2.C_USER_TYPE,
                            SUM(N_AMOUNT) AS N_AMOUNT,
                            SUM(N_USEAMOUNT) AS N_USEAMOUNT
                        FROM QS_PLANWEEKAPPLY T1
                        INNER JOIN QS_PLAN_DETAIL2 T2 on T1.N_WEEKAPP_ID = T2.N_PLANAPP_ID  AND T2.N_TYPE = 3
                        INNER JOIN QS_PLANMONTHAPPLY T3 ON T1.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        WHERE
                            T3.N_YEAR = :YEAR AND
                            T3.N_MONTH = :MONTH AND
                            T1.N_BUSINESS_STATE = 2 AND
                            T3.N_COMPANY_ID = :COMPANY_ID
                        GROUP BY
                            T2.C_USER_TYPE
                        ORDER BY
                            T2.C_USER_TYPE";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":MONTH", month, "int");
            parms.Add(":COMPANY_ID", companyId, "int");
            var dt = Query(sql, parms);
            return dt;
        }

        /// <summary>
        /// 获取月资金信息(proType:1安装外包费用;2材料费用;3大修费用4城市管网项目)
        /// </summary>
        public DataTable GetData_3(int companyId, int year, int month, int proType)
        {
            var sql = @"SELECT
                            T2.C_PRONAME,
                            T2.C_CUSTOMER_NAME,
                            SUM(T2.N_REAMOUNT) AS N_REAMOUNT,
                            SUM(T2.N_USEAMOUNT) AS N_USEAMOUNT
                        FROM QS_PLANWEEKAPPLY T1
                        INNER JOIN QS_PLAN_DETAIL3 T2 on T1.N_WEEKAPP_ID = T2.N_PLANAPP_ID  AND T2.N_TYPE = 3
                        INNER JOIN QS_PLANMONTHAPPLY T3 ON T1.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        WHERE
                            T3.N_YEAR = :YEAR AND
                            T3.N_MONTH = :MONTH AND
                            T1.N_BUSINESS_STATE = 2 AND
                            T3.N_COMPANY_ID = :COMPANY_ID AND
                            T2.N_PROTYPE = :PROTYPE
                        GROUP BY
                            T2.C_PRONAME,
                            T2.C_CUSTOMER_NAME
                        ORDER BY
                            T2.C_PRONAME";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":MONTH", month, "int");
            parms.Add(":COMPANY_ID", companyId, "int");
            parms.Add(":PROTYPE", proType, "int");
            var dt = Query(sql, parms);
            return dt;
        }

        /// <summary>
        /// 获取月资金信息
        /// </summary>
        public DataTable GetData(int companyId, int year, int month)
        {
            #region 注释

            //var sql = @"SELECT
            //                T4.N_MDETAIL_ID,
            //                T4.N_PROID,
            //                T4.C_PRONAME,
            //                T4.N_TYPE,
            //                T4.N_NUM1,
            //                T4.N_NUM2,
            //                T4.C_PRONUM,
            //                T4.N_TYPE2,
            //                T4.N_IF_DETAILS,
            //                T4.C_DETAILS_CODE,
            //                T4.N_PARENTID,
            //                SUM(T2.N_AMOUNT) AS N_AMOUNT,
            //                SUM(T2.N_USEAMOUNT) AS N_USEAMOUNT
            //            FROM QS_PLANWEEKAPPLY T1
            //            INNER JOIN QS_PLANWEEK_DETAIL T2 ON T1.N_WEEKAPP_ID = T2.N_WEEKAPP_ID
            //            INNER JOIN QS_PLANMONTHAPPLY T3 ON T1.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
            //            INNER JOIN QS_PLANMONTH_DETAIL T4 ON T2.N_PROID = T4.N_PROID AND T3.N_MONTHAPP_ID = T4.N_MONTHAPP_ID
            //            WHERE
            //                T3.N_YEAR = :YEAR AND
            //                T3.N_MONTH = :MONTH AND
            //                T1.N_BUSINESS_STATE = 2 AND
            //                T3.N_COMPANY_ID = :COMPANY_ID
            //            GROUP BY
            //                T4.N_MDETAIL_ID,
            //                T4.N_PROID,
            //                T4.C_PRONAME,
            //                T4.N_TYPE,
            //                T4.N_NUM1,
            //                T4.N_NUM2,
            //                T4.C_PRONUM,
            //                T4.N_TYPE2,
            //                T4.N_IF_DETAILS,
            //                T4.C_DETAILS_CODE,
            //                T4.N_PARENTID
            //            ORDER BY
            //                T4.N_MDETAIL_ID";

            #endregion 注释

            var sql = @"SELECT
                            T5.N_YDETAIL_ID,
                            T5.N_PROID,
                            T5.C_PRONAME,
                            T5.N_TYPE,
                            T5.N_NUM1,
                            T5.N_NUM2,
                            T5.C_PRONUM,
                            T5.N_TYPE2,
                            T5.N_IF_DETAILS,
                            T5.C_DETAILS_CODE,
                            T5.N_PARENTID,
                            SUM(T2.N_AMOUNT) AS N_AMOUNT,
                            SUM(T2.N_USEAMOUNT) AS N_USEAMOUNT
                        FROM QS_PLANWEEKAPPLY T1
                        INNER JOIN QS_PLANWEEK_DETAIL T2 ON T1.N_WEEKAPP_ID = T2.N_WEEKAPP_ID
                        INNER JOIN QS_PLANMONTHAPPLY T3 ON T1.N_MONTHAPP_ID = T3.N_MONTHAPP_ID
                        INNER JOIN QX_PLANYEARAPPLY T4 ON T4.N_YEARAPP_ID=T1.N_YEARAPP_ID
                        INNER JOIN QX_PLANYEAR_DETAIL T5 ON T2.N_PROID = T5.N_PROID AND T4.N_YEARAPP_ID = T5.N_YEARAPP_ID
                        WHERE
                            T3.N_YEAR = :YEAR AND
                            T3.N_MONTH = :MONTH AND
                            T1.N_BUSINESS_STATE = 2 AND
                            T3.N_COMPANY_ID = :COMPANY_ID
                        GROUP BY
                            T5.N_YDETAIL_ID,
                            T5.N_PROID,
                            T5.C_PRONAME,
                            T5.N_TYPE,
                            T5.N_NUM1,
                            T5.N_NUM2,
                            T5.C_PRONUM,
                            T5.N_TYPE2,
                            T5.N_IF_DETAILS,
                            T5.C_DETAILS_CODE,
                            T5.N_PARENTID
                        ORDER BY
                            T5.N_YDETAIL_ID";

            //定义变量来接收集合
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":YEAR", year, "int");
            parms.Add(":MONTH", month, "int");
            parms.Add(":COMPANY_ID", companyId, "int");
            var dt = Query(sql, parms);
            return dt;
        }
    }

    /// <summary>
    /// 月资金申请接口
    /// </summary>
    public interface IPlanMonthApply
    {
        /// <summary>
        /// 获取资金申请信息
        /// </summary>
        /// <param name="comId">公司Id</param>
        /// <param name="year">年份</param>
        /// <returns></returns>
        IResonResult GetData(int comId, int year);

        /// <summary>
        /// 月资金申请
        /// </summary>
        /// <param name="applyId">申请Id</param>
        /// <param name="userid">申请人用户Id</param>
        /// <returns></returns>
        IResonResult Apply(int applyId, int userid);

        /// <summary>
        /// 创建月资金申请信息
        /// </summary>
        /// <param name="comId">公司Id</param>
        /// <param name="year">年份</param>
        /// <param name="month">月</param>
        /// <param name="userid">申请人用户Id</param>
        /// <returns></returns>
        IResonResult Create(int comId, int year, int month, int userid);
    }
}